<?php
/*
    ID:   $Id: $
    File: URLToSQL.php
    Name: John Griffin
    Date: May 2, 2009
    
    Notes:
        array ('var1' => 'type
*/
require_once 'BreakdownException.php';

class URLToSQL {
    private $resource = null;
    private $error = null;
    
    public function __construct (SimpleXMLElement $resource, Error $error) {
        $this->resource = $resource;
        $this->error = $error;
    }
        
    private function pcre_error_deocde() {
        switch (preg_last_error()) {
          case PREG_PATTERN_ORDER:
              print "Orders results so that $matches[0] is an array of full pattern matches, $matches[1] is an array of strings matched by the first parenthesized subpattern, and so on. This flag is only used with preg_match_all().\n";
              break;
          case PREG_SET_ORDER:
              print "Orders results so that $matches[0] is an array of first set of matches, $matches[1] is an array of second set of matches, and so on. This flag is only used with preg_match_all().\n";
              break;
          case PREG_OFFSET_CAPTURE:
              print "See the description of PREG_SPLIT_OFFSET_CAPTURE. This flag is available since PHP 4.3.0.\n";
              break;
          case PREG_SPLIT_NO_EMPTY:
              print "This flag tells preg_split() to return only non-empty pieces.\n";
              break;
          case PREG_SPLIT_DELIM_CAPTURE:
              print "This flag tells preg_split() to capture parenthesized expression in the delimiter pattern as well. This flag is available since PHP 4.0.5.\n";
              break;
          case PREG_SPLIT_OFFSET_CAPTURE:
              print "If this flag is set, for every occurring match the appendant string offset will also be returned. Note that this changes the return values in an array where every element is an array consisting of the matched string at offset 0 and its string offset within subject at offset 1. This flag is available since PHP 4.3.0 and is only used for preg_split().\n";
              break;
          case PREG_NO_ERROR:
              // do not print in this case
              print "Returned by preg_last_error() if there were no errors. Available since PHP 5.2.0.\n";
              break;
          case PREG_INTERNAL_ERROR:
              print "Returned by preg_last_error() if there was an internal PCRE error. Available since PHP 5.2.0.\n";
              break;
          case PREG_BACKTRACK_LIMIT_ERROR:
              print "Returned by preg_last_error() if backtrack limit was exhausted. Available since PHP 5.2.0.\n";
              break;
          case PREG_RECURSION_LIMIT_ERROR:
              print "Returned by preg_last_error() if recursion limit was exhausted. Available since PHP 5.2.0.\n";
              break;
          case PREG_BAD_UTF8_ERROR:
              print "Returned by preg_last_error() if the last error was caused by malformed UTF-8 data (only when running a regex in UTF-8 mode). Available since PHP 5.2.0.\n";
              break;
          case PREG_BAD_UTF8_OFFSET_ERROR:
              print "Returned by preg_last_error() if the offset didn't correspond to the begin of a valid UTF-8 code point (only when running a regex in UTF-8 mode). Available since PHP 5.3.0.\n";
              break;
          case PCRE_VERSION:
              print "PCRE version and release date (e.g. '7.0 18-Dec-2006'). Available since PHP 5.2.4.\n";
              break;
        }
    }

    private function parseQueryInt ($name, $value) {
        $return = '';
        if (preg_match ('/^-{0,1}\d+$/', $value)) {                                             // integer or -integer
            $return = $name.' = '.$value;
        } elseif (preg_match ('/^\[-{0,1}\d+:]$/', $value)) {                                   // [n:]
            $return = $name.' >= '.substr ($value, 1,  -2);
        } elseif (preg_match ('/^\[:-{0,1}\d+]$/', $value)) {                                   // [:m]
            $return = $name.' <= '.substr ($value, 2, -1);
        } elseif (preg_match ('/^\[-{0,1}\d+:-{0,1}\d+]$/', $value)) {                           // [n:m]
            $return = $name.' BETWEEN '.str_replace (':', ' AND ', substr ($value, 1, -1));
        } elseif (preg_match ('/^\{(-{0,1}\d+,*[[:space:]]*)*}$/', $value)) {            // {a,b,c,d}
            $return = $name.' IN ('.substr ($value, 1, -1).')';
        } else {
            throw new BreakdownException ($value, BreakdownException::BAD_INTEGER_OR_EXPRESSION);
        }
        return $return;
    }

    private function parseQueryDate ($name, $value) {
        $return = '';
        if (preg_match ('/^(19|20)[0-9]{2}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$/', $value)) {                                             // YYYY-MM-DD
            $return = $name." = '".$value."'";
        } elseif (preg_match ('/^\[(1[0-9]|20)\d\d-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]):]$/', $value)) {                                   // [n:]
            $return = $name." >= '".substr ($value, 1,  -2)."'";
        } elseif (preg_match ('/^\[:(1[0-9]|20)\d\d-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])]$/', $value)) {                                   // [:m]
            $return = $name." <= '".substr ($value, 2, -1)."'";
        } elseif (preg_match ('/^\[(1[0-9]|20)\d\d-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]):(1[0-9]|20)\d\d-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])]$/', $value)) {                           // [n:m]
            $return = $name." BETWEEN '".str_replace (':', "' AND '", substr ($value, 1, -1))."'";
        } elseif (preg_match ('/^\{((1[0-9]|20)\d\d-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]),*[[:space:]]*)+}$/', $value)) {            // {a,b,c,d}
            $return = $name." IN ('".preg_replace ('/,[[:space:]]*/', "', '", substr ($value, 1, -1))."')";
        } else {
            throw new BreakdownException ($value, BreakdownException::BAD_DATE_OR_EXPRESSION);
        }
        return $return;
    }
    
    private function parseQueryString ($name, $value) {
        $return = '';
        if ($value == '') {                 // need test for sql injection
            $return = $name.' IS NULL';
        } elseif (strpos ($value, '%') === false && strpos ($value, '_') === false ) {
            $return = $name." = '".$value."'";
        } else {
            $return = $name." LIKE '".$value."'";
        }
        return $return;
    }
    
    private function parseQueryFilter (array $fields, array $filter) {
        static $parse = array ('int' => 'parseQueryInt', 'date' => 'parseQueryDate', 'string' => 'parseQueryString');
        $return = array ();
        foreach ($fields as $key => $value) {
            if (isset ($filter[$key]) == true) {
                assert (isset ($parse[$value]) == true);
                $return[] = $this->$parse[$value] ($key, $filter[$key]);
            }
        }
        return $return;
    }
    
    private function parseQueryModifiers (array $modifiers) {
        $count = 10;
        $start = 0;
        $return = array ();
        if (isset ($modifiers['count']) == true) {
            if (preg_match ('/^\d+$/', $modifiers['count'])) {
                $count = $modifiers['count'];
            } else {
                throw new BreakdownException ($modifiers['count'], BreakdownException::BAD_INTEGER_OR_EXPRESSION);
            }
        }
        if (isset ($modifiers['start']) == true) {
            if (preg_match ('/^\d+$/', $modifiers['start'])) {
                $start = $modifiers['start'];
            } else {
                throw new BreakdownException ($modifiers['start'], BreakdownException::BAD_INTEGER_OR_EXPRESSION);
            }
        }
        $return[] = "LIMIT $count, $start";
        if (isset ($modifiers['order']) == true) {
                throw new BreakdownException ('order', BreakdownException::NOT_IMPLEMENTED);
        }
        return $return;
    }
    
    private function getFieldtypes () {
        $return = array ();
        foreach ($this->resource->field as $value) {

            $return[(string) $value['name']] = (string) $value['type'];
        }
        return $return;
    }
    
    public function buildQuery (array $filter, array $modifiers = array ()) {
    
        $fields = $this->getFieldtypes ();
        $sql = 'SELECT '.implode (', ', array_keys ($fields))
             . ' FROM '.(string) $this->resource['name'];
        $return = $this->parseQueryFilter ($fields, $filter);
        if (count ($return) > 0) {
            $sql .= ' WHERE '.implode (' AND ', $return);
        }
        
        // see http://forums.oracle.com/forums/thread.jspa?messageID=1570364 on how to do a limit clause in Oracle
        $return = $this->parseQueryModifiers ($modifiers);
        if (count ($return) > 0) {
            $sql .= ' '.implode (' ', $return);
        }
        return $sql;
    }
    
    private function validateValue ($field, $value) {
        $return = false;
        switch ($field['type']) {
            case 'int':
                $return = (preg_match ('/^-{0,1}\d+$/', $value)) ? true : false;
                break;
            case 'date':
                $return = (preg_match ('/^(19|20)[0-9]{2}-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])$/', $value)) ? true : false;
                break;
            case 'string':
                $return = true;     // need test for sql injection
            default:
                throw new BreakdownException ($field['type'], BreakdownException::INVALID_FIELD_TYPE);
        }
        return $return;
    }

    public function buildInsert (array $record) {
        $sql = '';
        $columns = array ();
        $unique_key = $this->resource->uniqueKey;
        foreach ($this->resource->field as $field) {
            if ($field['name'] == $unique_key) {
                continue;
            } else {
                if (isset ($record[$field['name']]) == true) {
                    $columns[] =  $field['name'];
                }
            }
        }
        if (count ($error) == 0) {
            $sql = 'INSERT INTO'.(string) $this->resource['name'].' ('.implode (', ', $columns).') VALUES (:'.implode (', :', $columns).')';
        }
        return $sql;
    }

    public function buildUpdate (array $record) {
        $sql = '';
        $columns = array ();
        $unique_key = $this->resource->uniqueKey;
        foreach ($this->resource->field as $field) {
            if ($field['name'] == $unique_key) {
                continue;
            } else {
                if (isset ($record[$field['name']]) == true) {
                    $columns[] =  $field['name'].' = :'.$field['name'];
                }
            }
        }
        if (count ($error) == 0) {
            $sql = 'UPDATE '.(string) $this->resource['name'].' SET'.implode (', ', $columns).' WHERE '.$unique_key.' = '.$record[$unique_key];
        }
        return $sql;
    }

    public function buildDelete (array $ids) {
        $unique_key = $this->resource->uniqueKey;
        $sql = 'DELETE FROM '.(string) $this->resource['name'].' WHERE '.$unique_key.' IN ('.implode (', ', $ids).')';
        return $sql;
    }
}
?>
